﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Configuration;
using System.Data;

namespace PracticeWcf
{
    internal class BatteryReservationData
    {
        private String connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        internal bool ReserveBattery(BatteryRequest batteryRequest)
        {
            SqlConnection connection = GetConnection();
            String sqlCommand = "INSERT INTO BatteryRequest(location_ID, booked, charged, user_ID) VALUES("+" @location_ID, @booked, @charged, @user_ID)";

            SqlCommand command = connection.CreateCommand();
            command.CommandText = sqlCommand;
            command.Parameters.Add("@location_ID", System.Data.SqlDbType.Int);
            command.Parameters.Add("@booked", System.Data.SqlDbType.Bit);
            command.Parameters.Add("@charged", System.Data.SqlDbType.Bit);
            command.Parameters.Add("@user_ID", System.Data.SqlDbType.Int);

            command.Parameters["@location_ID"].Value = batteryRequest.location_ID;
            command.Parameters["@booked"].Value = batteryRequest.booked;
            command.Parameters["@charged"].Value = batteryRequest.charged;
            command.Parameters["@user_ID"].Value = batteryRequest.user_ID;

            int rowsEffected = 0;
            try
            {
                rowsEffected = command.ExecuteNonQuery();
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return rowsEffected > 0;
        }

        internal BatteryRequest[] getReservations(Boolean booked, Boolean charged)
        {
            List<BatteryRequest> requestedBattery = new List<BatteryRequest>();
            SqlConnection connection = GetConnection();
            SqlCommand command = connection.CreateCommand();

            command.CommandText = "SELECT location_ID, booked, charged, user_ID " + " FROM Battery" + "WHERE booked == false AND  charged == true";

            SqlDataReader reader = null;

            try
            {
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    BatteryRequest batteryRequest = new BatteryRequest();
                    batteryRequest.location_ID = Convert.ToInt16(reader[0]);
                    batteryRequest.booked = Convert.ToBoolean(reader[1]);
                    batteryRequest.charged = Convert.ToBoolean(reader[2]);
                    batteryRequest.user_ID = Convert.ToInt16(reader[3]);

                    requestedBattery.Add(batteryRequest);
                }

            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }

                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return requestedBattery.ToArray();
        }
            
            private SqlConnection GetConnection()
            {
                SqlConnection connection = new SqlConnection(connectionString);
                try
                {
                    connection.Open();
                }
                finally
                {
                }
                return connection;
            }

        }
        
    }
